/*
	EXEC sp_BBB_report_proc '01/01/2011','05/31/2011','0','0','0','0','0','0',1,'0','XLREPORT'

*/
ALTER PROC sp_BBB_report_proc

@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@CUSTOMERID	VARCHAR(200),
@PRODUCTID	VARCHAR(200),
@REGION		VARCHAR(50),
@REPOFFICE	VARCHAR(50),
@ENDCUSTOMER	VARCHAR(500),--parameter for end customer
@SALESPERSON	VARCHAR(500),
@ORDERTYPE	INT,
@ENDUSER	VARCHAR(500),--parameter for end user
@REPORTTYPE	VARCHAR(50)

AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #BOOKINGTABLE 
		( SALESORDERID INT,[LINEITEM ID] INT, [CHANGE NO] INT,[LINE ITEM NO] INT, PRODUCTID INT, BOOKING_QTY INT, REMAINING_QTY INT, ACTUAL_SHIP_QTY INT,
		  [UNIT PRICE] FLOAT,BACKLOG_AMOUNT FLOAT, BOOKING_AMOUNT FLOAT, SHIPPING_AMOUNT FLOAT, 
		  [REQUESTED DOCK DATE] DATETIME, [SCHEDULE DATE] DATETIME, [REVISED DATE] DATETIME,
		  [BOOKING DATE] DATETIME, [CHANGE DATE] DATETIME, [SHIP DATE] DATETIME, [CONFIRM DATE] DATETIME,
		  [SALES ORDER NO] VARCHAR(50),CUSTOMERID INT,[SALES PERSON] VARCHAR(50),[END CUSTOMER] VARCHAR(65),
		  [CUSTOMER PO NUMBER] VARCHAR(50),[REGION REP] VARCHAR(50),[END USER] VARCHAR(65),CUSTOMER VARCHAR(65), [PRODUCT] VARCHAR(50),
		  [MRKT PART NUMBER] VARCHAR(50) )
				
	CREATE TABLE #INITIAL_RESULTS
		(SALESORDER_ID INT, LINEITEM_ID INT, CHANGENO INT, QTY INT, UNITCOST FLOAT )

	DECLARE @CHANGE_LINEITEMID INT,@CHANGE_SALESORDERID INT,@CHANGENO INT,@CHANGE_QTY INT,@CHANGE_UNIT_COST FLOAT,@CHANGE_AMOUNT FLOAT
	DECLARE @QUANTITY INT,@UNITCOST FLOAT,@AMOUNT FLOAT
	DECLARE @INITIAL_UNIT_COST FLOAT, @INSERT_UNIT_COST FLOAT, @INSERT_AMOUNT FLOAT
	DECLARE @INITIAL_QTY INT, @INSERT_QTY INT, @INSERTCHANGE_NO INT
	DECLARE @PRODUCT_ID INT,@CCO_REQUESTED_DATE DATETIME,@CCO_BOOKING_DATE DATETIME,@CCO_SCHEDULE_DATE DATETIME,@CCO_REVISED_DATE DATETIME,@BOOKINGDATE DATETIME
	DECLARE @LINE_ITEM_NO VARCHAR(50)
	DECLARE @SALES_ORDER_NO VARCHAR(50),@CUSTOMER_ID INT,@SALES_PERSON VARCHAR(50),@END_CUSTOMER VARCHAR(65),
	@REGION_REP VARCHAR(50),@END_USER VARCHAR(65),@CUSTOMER VARCHAR(65),@PRODUCT VARCHAR(50),@MRKT_PART_NUMBER VARCHAR(50)
	DECLARE @Customer_PO_Number varchar(50)
	DECLARE @ISCONSIGNMENT BIT

	DECLARE @BOOKINGSTRING 		VARCHAR(4000)
	DECLARE @BOOKINGFROMSTRING	VARCHAR(4000)
	DECLARE @CANCELSTRING 		VARCHAR(4000)
	DECLARE @CANCELFROMSTRING 	VARCHAR(4000)
	DECLARE @RESULT_STR NVARCHAR(4000)

/* BOOKING REPORT STARTS HERE */
	SET @BOOKINGSTRING = N'
	insert into #BOOKINGTABLE
	SELECT
		 ccol.Salesorder_ID, LineItem_ID, ChangeNo, CCO_Line_Item_No, ccol.Product_ID, CCO_Qty, 0, 0, CCO_Unit_Cost, 0.0,(CCO_Qty*CCO_Unit_Cost), 0.0,
		 CCO_Requested_Date, CCO_Schedule_Date, CCO_Revised_Date, SO_Booking_Date, CCOL.TIMESTAMP, '''','''',
		 SO.SalesOrder_NO,SO.Customer_ID,SO.SO_Sales_Person, 
		 CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  )
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
	 	 END,
		SO.SO_Customer_PO_NUMBER, SO.Regional_Rep,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
		THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END) 
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END, C_NAME, PART_NUMBER ,P_MARKETING_PART_NUMBER '

	SET @BOOKINGFROMSTRING = N'		
	FROM customer_change_order_lineitem CCOL, SALES_ORDER SO, customer c, product p
	WHERE CCOL.SALESORDER_ID = SO.SALESORDER_ID and so.customer_id = c.customer_id and ccol.product_id = p.product_id 
	and changeNo = 0 '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND ccol.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C.C_Region ) = '''+ @REGION+''''
	END
	IF @REPOFFICE IS NOT NULL AND @REPOFFICE <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDCUSTOMER IS NOT NULL AND @ENDCUSTOMER <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
						 	 END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END
	IF @SALESPERSON IS NOT NULL AND @SALESPERSON <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END

	PRINT (@BOOKINGSTRING)
	PRINT (@BOOKINGFROMSTRING)
	EXEC (@BOOKINGSTRING+@BOOKINGFROMSTRING)

	--NEED TO DELETE LINEITEMS WHICH DOES NOT FALL IN CHANGE DATE
	DECLARE @DELETE_STR NVARCHAR(500)
	SET @DELETE_STR = 'DELETE FROM #BOOKINGTABLE WHERE [LINEITEM ID] NOT IN ( SELECT DISTINCT LINEITEM_ID FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE 1=1 '
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @DELETE_STR = @DELETE_STR + ' AND TIMESTAMP >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @DELETE_STR = @DELETE_STR + ' AND TIMESTAMP <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	SET @DELETE_STR = @DELETE_STR + ' )'

	EXEC (@DELETE_STR)

	INSERT INTO #INITIAL_RESULTS (SalesOrder_ID, LineItem_ID, ChangeNo, Qty, UnitCost )
		select SALESORDERID,[LINEITEM ID],[CHANGE NO],BOOKING_QTY,[UNIT PRICE] from #BOOKINGTABLE

	DECLARE CHANGECURSOR CURSOR FOR 
		SELECT 
			LINEITEM_ID,SALESORDER_ID,CHANGENO,CCO_QTY,CCO_UNIT_COST--,CCO_AMOUNT 
		FROM 
			CUSTOMER_CHANGE_ORDER_LINEITEM 
		WHERE 
			CHANGENO <> 0
			AND LINEITEM_ID IN ( SELECT [LINEITEM ID] FROM #BOOKINGTABLE)
		ORDER BY CHANGENO

	OPEN CHANGECURSOR
	FETCH NEXT FROM CHANGECURSOR INTO @CHANGE_LINEITEMID,@CHANGE_SALESORDERID,@CHANGENO,@CHANGE_QTY,@CHANGE_UNIT_COST--,@CHANGE_AMOUNT
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT 	@INSERTCHANGE_NO = MAX(ChangeNo) FROM #INITIAL_RESULTS 
		WHERE SalesOrder_ID = @CHANGE_SALESORDERID AND LINEITEM_ID = @CHANGE_LINEITEMID

		SELECT @initial_Qty = Qty,@initial_Unit_Cost = UnitCost
		FROM #INITIAL_RESULTS 
		WHERE 
			SalesOrder_ID = @CHANGE_SALESORDERID 
			AND LINEITEM_ID = @CHANGE_LINEITEMID
			AND ChangeNo = @INSERTCHANGE_NO

		SELECT 
			@SALES_ORDER_NO = [SALES ORDER NO],@CUSTOMER_ID = CUSTOMERID,@SALES_PERSON = [SALES PERSON],@END_CUSTOMER = [END CUSTOMER],
					  @REGION_REP = [REGION REP],@END_USER = [END USER],@CUSTOMER = CUSTOMER, @PRODUCT = [PRODUCT],@MRKT_PART_NUMBER = [MRKT PART NUMBER],
					  @BOOKINGDATE = [BOOKING DATE], @Customer_PO_Number = [CUSTOMER PO NUMBER] 
		FROM 
			#BOOKINGTABLE 
		WHERE 
			[LINEITEM ID] = @CHANGE_LINEITEMID 
			AND SALESORDERID = @CHANGE_SALESORDERID 
			AND [CHANGE NO] = @INSERTCHANGE_NO

		SELECT 
			@product_id = product_id,@LINE_ITEM_NO = CCO_LINE_ITEM_NO,@cco_requested_date = cco_requested_date,
			@cco_booking_date = [TIMESTAMP],@cco_schedule_date = cco_schedule_date,@cco_revised_date = cco_revised_date
		FROM 
			CUSTOMER_CHANGE_ORDER_LINEITEM 
		where 
			LINEITEM_ID = @CHANGE_LINEITEMID AND SALESORDER_ID = @CHANGE_SALESORDERID AND CHANGENO = @CHANGENO


		--look for the changes in amt or qty or price
		set @insert_Unit_Cost = @CHANGE_UNIT_COST - @initial_Unit_Cost

		set @insert_Qty = @CHANGE_QTY - @initial_Qty
											
		--If there are no changes then set them back to default
		if @insert_Unit_Cost = 0
			set @insert_Unit_Cost = @CHANGE_UNIT_COST
		if @insert_Qty = 0
			set @insert_Qty = @CHANGE_QTY

		--set @insert_Amount = @insert_Unit_Cost * @insert_Qty

		insert into #INITIAL_RESULTS values (@CHANGE_SALESORDERID, @CHANGE_LINEITEMID, @CHANGENO, @CHANGE_QTY,@CHANGE_UNIT_COST)
		
		if( @CHANGE_QTY <> @initial_Qty )
		begin
			INSERT INTO #BOOKINGTABLE 
					( SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,BOOKING_QTY,
					  [UNIT PRICE],BOOKING_AMOUNT,[REQUESTED DOCK DATE],
					  [SCHEDULE DATE], [REVISED DATE], [BOOKING DATE], [CHANGE DATE], [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
					  [CUSTOMER PO NUMBER], [REGION REP],[END USER],CUSTOMER, [PRODUCT],[MRKT PART NUMBER])

				VALUES 	( @CHANGE_SALESORDERID,@CHANGE_LINEITEMID,@CHANGENO,@LINE_ITEM_NO, @product_id,@insert_Qty,
					  @CHANGE_UNIT_COST,(@insert_Qty*@CHANGE_UNIT_COST),@cco_requested_date,
					  @cco_schedule_date, @cco_revised_date, @BOOKINGDATE,  @cco_booking_date,
					  @SALES_ORDER_NO,@CUSTOMER_ID,@SALES_PERSON,@END_CUSTOMER,
					  @Customer_PO_Number, @REGION_REP,@END_USER,@CUSTOMER, @PRODUCT,@MRKT_PART_NUMBER )
		end

		if(@CHANGE_UNIT_COST <> @initial_Unit_Cost)
		begin
			INSERT INTO #BOOKINGTABLE 
					( SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,BOOKING_QTY,
					  [UNIT PRICE],BOOKING_AMOUNT,[REQUESTED DOCK DATE],
					  [SCHEDULE DATE], [REVISED DATE], [BOOKING DATE], [CHANGE DATE], [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
					  [CUSTOMER PO NUMBER], [REGION REP],[END USER],CUSTOMER, [PRODUCT],[MRKT PART NUMBER])
				VALUES 	( @CHANGE_SALESORDERID,@CHANGE_LINEITEMID,@CHANGENO,@LINE_ITEM_NO, @product_id,@initial_Qty,
					  @insert_Unit_Cost,(@initial_Qty*@insert_Unit_Cost),@cco_requested_date,
					  @cco_schedule_date,  @cco_revised_date,@BOOKINGDATE,@cco_booking_date,
					  @SALES_ORDER_NO,@CUSTOMER_ID,@SALES_PERSON,@END_CUSTOMER,
					  @Customer_PO_Number, @REGION_REP,@END_USER,@CUSTOMER, @PRODUCT,@MRKT_PART_NUMBER)
		end


		if (@CHANGE_QTY = @initial_Qty and @CHANGE_UNIT_COST = @initial_Unit_Cost ) 
		begin
			INSERT INTO #BOOKINGTABLE 
					( SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,BOOKING_QTY,
					  [UNIT PRICE],BOOKING_AMOUNT,[REQUESTED DOCK DATE],
					  [SCHEDULE DATE], [REVISED DATE], [BOOKING DATE], [CHANGE DATE], [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
					  [CUSTOMER PO NUMBER], [REGION REP],[END USER],CUSTOMER, [PRODUCT],[MRKT PART NUMBER])
				VALUES 	( @CHANGE_SALESORDERID,@CHANGE_LINEITEMID,@CHANGENO,@LINE_ITEM_NO, @product_id,0,
					  0.00,0.00,@cco_requested_date,
					  @cco_schedule_date, @cco_revised_date, @BOOKINGDATE, @cco_booking_date,
					  @SALES_ORDER_NO,@CUSTOMER_ID,@SALES_PERSON,@END_CUSTOMER,
					  @Customer_PO_Number, @REGION_REP,@END_USER,@CUSTOMER, @PRODUCT,@MRKT_PART_NUMBER )
		end
		
			SET @CHANGE_SALESORDERID = NULL
			SET @CHANGE_LINEITEMID = NULL
			SET @CHANGENO = NULL
			SET @LINE_ITEM_NO = NULL
			SET @product_id = NULL
			SET @insert_Qty = NULL
			SET @insert_Unit_Cost = NULL
			SET @cco_requested_date = NULL
			SET @cco_schedule_date = NULL
			SET @cco_revised_date = NULL
			SET @cco_booking_date = NULL
			SET @SALES_ORDER_NO = NULL
			SET @CUSTOMER_ID = NULL
			SET @SALES_PERSON = NULL
			SET @END_CUSTOMER = NULL
			SET @REGION_REP = NULL
			SET @END_USER = NULL
			SET @CUSTOMER = NULL
			SET @PRODUCT = NULL
			SET @MRKT_PART_NUMBER = NULL

			SET @initial_Unit_Cost = NULL
			SET @insert_Unit_Cost = NULL
			SET @insert_Amount = NULL
			SET @initial_Qty = NULL
			SET @insert_Qty  = NULL
			SET @INSERTCHANGE_NO  = NULL
			SET @ISCONSIGNMENT = NULL

	FETCH NEXT FROM CHANGECURSOR INTO @CHANGE_LINEITEMID,@CHANGE_SALESORDERID,@CHANGENO,@CHANGE_QTY,@CHANGE_UNIT_COST--,@CHANGE_AMOUNT
	END
	CLOSE CHANGECURSOR
	DEALLOCATE CHANGECURSOR

	set @CANCELSTRING = N' 
	insert into #BOOKINGTABLE (SALESORDERID,[LINEITEM ID],[LINE ITEM NO],PRODUCTID,BOOKING_QTY,
		  [UNIT PRICE],BOOKING_AMOUNT,[REQUESTED DOCK DATE],
		  [SCHEDULE DATE], [REVISED DATE], [BOOKING DATE], [CHANGE DATE],
		  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
		  [CUSTOMER PO NUMBER], [REGION REP],[END USER],CUSTOMER, [PRODUCT], [MRKT PART NUMBER] )
	select 
		CCOL.Salesorder_ID, LineItem_ID, CCOL_Line_Item_No, ccol.Product_ID,(CCOL_Qty*-1), (CCOL_Unit_Cost*-1),((CCOL_Qty*-1)*(CCOL_Unit_Cost)),  
		CCOL_Requested_Date, CCOL_Schedule_Date, CCOL_Revised_Date , so_Booking_Date,
		CCOL.Timestamp,	SO.SalesOrder_NO,SO.Customer_ID,SO.SO_Sales_Person,
		CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END, SO.SO_Customer_PO_Number, Regional_Rep,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
			THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
				THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END) 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END,
		C_NAME,PART_NUMBER,P_MARKETING_PART_NUMBER '

	SET @CANCELFROMSTRING = N' 
	from  
		customer_cancel_order_lineitem CCOL,Sales_Order SO, customer c, product p
	where 
		CCOL.SalesOrder_Id = SO.SalesOrder_Id 
		and ccol.product_id = p.product_id
		and so.customer_id = c.customer_id '

	IF @PRODUCTID <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND CCOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND C.C_REGION = '''+ @REGION+''''
	END
	IF @REPOFFICE <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDUSER <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	IF @SALESPERSON <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDCUSTOMER <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END
	
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING +' AND CCOL.Timestamp >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + +' AND CCOL.Timestamp <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	--print '@CANCELSTRING = '+@CANCELSTRING
	exec (@CANCELSTRING+@CANCELFROMSTRING)

 /*BOOKING REPORT ENDS HERE */

/* BACKLOG REPORT STARTS HERE */


		DECLARE @BACKLOGSELECTSTRING 	NVARCHAR(4000)
		DECLARE @BACKLOGFROMSTRING 		NVARCHAR(4000)

		DECLARE @START_DATE 	DATETIME
		DECLARE @END_DATE 	DATETIME
		DECLARE @TEMP_VARCHAR 	VARCHAR(20)
		
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @TODATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @TODATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)	


		IF ( @TODATE >= CONVERT(varchar, Current_timestamp, 101 ) )-- GET CURRENT BACKLOG
		BEGIN
			SET @BACKLOGSELECTSTRING = N' INSERT INTO #BOOKINGTABLE 
			select 	
			SO.SalesOrder_ID, COOL.LineItem_ID, 0,cool_line_item_no, P.Product_Id, 0, 
			(COOL.COOL_QTY - ( SELECT ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = ''SHIPPED'' )), 0,
			cool.cool_unit_cost, ( (COOL.COOL_QTY - ( SELECT ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = ''SHIPPED'' )) * cool.cool_unit_cost ), 0.0, 0.0,
			cool.cool_requested_date,cool.cool_schedule_date,ISNULL(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE), so.SO_Booking_Date,'''', '''', '''',
			so.salesorder_no, C.Customer_ID, so.so_sales_person, 
			(CASE 	
				WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
	 		END),so.so_customer_PO_number, so.Regional_Rep,
			(CASE 
				WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
					(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
					 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END)
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END), C.c_name,	p.part_number, p.P_marketing_Part_number '
		
				SET @BACKLOGFROMSTRING = N'		
				from 	
					CUSTOMER_OPEN_ORDER_LINEITEM COOL INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
					INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID
					INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID			
	   			where 
					cool.COOL_Status<> ''SHIPPED'' '
			
				IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2 )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND ( ( SO_IS_CONSIGNMENT = 0 ) OR ( C_IS_Consignee = 1 and SO_IS_CONSIGNMENT = 1 ) ) AND SO.SO_IS_SAMPLE = '''+ CONVERT(VARCHAR,@ORDERTYPE) +''''
				END
				ELSE IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2 )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND (  C_IS_Consignee = 0 and SO_IS_CONSIGNMENT = 1 ) '
				END

				IF ( @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO.CUSTOMER_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
				END	
				IF ( @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
				END

				IF ( @SALESPERSON IS NOT NULL AND @SALESPERSON <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO.SO_SALES_PERSON  IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
				END
				IF ( @REGION IS NOT NULL AND @REGION <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C.C_Region ) = '''+ @REGION +''''
				END		
				IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
										THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
											THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
											ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
											END) 
										ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
										END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
				END 
				--PRINT LEN(@BACKLOGFROMSTRING)
				--EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_BY_PERIOD = @BY_PERIOD,@PRM_CATEGORY = @CATEGORY,@PRM_ORDER_TYPE = @ORDER_TYPE,@PRM_PRODUCT_ID = @PRODUCT_ID,@PRM_SO_NUMBER = @SO_NUMBER,@PRM_CUSTOMER_ID = @CUSTOMER_ID,@PRM_SALES_PERSON = @SALES_PERSON,@PRM_BACKLOG_TYPE = @BACKLOG_TYPE
				print (@BACKLOGSELECTSTRING)
				print (@BACKLOGFROMSTRING)			
		END
		ELSE-- GET AS OF DATE BACKLOG
		BEGIN
				SET @BACKLOGSELECTSTRING = N' INSERT INTO #BOOKINGTABLE 
				 select 
					SO.SalesOrder_ID, LineItem_ID, 0, LineItem_No, Product_Id, 0,( BK_Requested_Qty - isNUll(BK_Shipped_Qty, 0) ), 0,
					BK_Unit_Cost, (( BK_Requested_Qty - isNUll(BK_Shipped_Qty, 0) ) * BK_Unit_Cost ), 0.0, 0.0,
					Req_Dock_Date, Schedule_Date, ISNULL(Revised_Date,Schedule_Date),Booking_Date, '''', '''', '''',
					BHT.SalesOrder_No, SO.Customer_ID,SALES_PERSON,
					(CASE 	WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
						THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
						ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 					END), Customer_PO_Number, BHT.REGIONAL_REP,
					(CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
							(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN 
								( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID ) 
				 				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID ) END)
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
					END),Customer_Name, Part_Number, Part_Number'
				SET @BACKLOGFROMSTRING = ' 
				from 
					Backlog_History_Table BHT, Sales_Order SO 
				where 
					BHT.SalesOrder_ID = SO.SalesOrder_ID 
					AND BHT.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
					
				--SET @SQLSTRING = @SQLSTRING + ' AND BHT.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''  
				
				IF ( @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO.CUSTOMER_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
				END	
				IF ( @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
				END
				IF ( @SALES_PERSON IS NOT NULL AND @SALES_PERSON <> '0' )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALES_PERSON +'''))'
				END

				IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2 )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_IS_SAMPLE = '''+ CONVERT(VARCHAR,@ORDERTYPE) +''''
				END
				ELSE IF ( @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2 )
				BEGIN
					SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
				END		
				print (@BACKLOGSELECTSTRING)
				print (@BACKLOGFROMSTRING)	
		END

	EXEC (@BACKLOGSELECTSTRING+@BACKLOGFROMSTRING)

/* BACKLOG REPORT ENDS HERE */

/* SHIPPING REPORT STARTS HERE */
	DECLARE @SHIPMENTSTRING NVARCHAR(4000)


	SET @SHIPMENTSTRING = N'
	INSERT INTO #BOOKINGTABLE 
	SELECT
		SO.SalesOrder_ID, COOL.LineItem_ID, 0, COOL.cool_line_item_no, COOL.Product_ID, 0, 0, CSLIT.CSLIT_Qty_Shipped, 
		COOL.COOL_Unit_Cost, 0.0, 0.0, (COOL.COOL_Unit_Cost * CSLIT.CSLIT_Qty_Shipped ),
		COOL.COOL_Requested_Date,COOL.COOL_Schedule_Date, ISNULL( COOL_REVISED_DATE, COOL_Schedule_Date ),
		SO.SO_Booking_Date,'''', CST.CST_Ship_Date,CST.CST_CONFIRMED_ON,
		SO.SalesOrder_No,SO.Customer_ID, SO_Sales_Person,
		(CASE 	
			WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END), SO.SO_Customer_PO_Number,SO.Regional_Rep,
		(CASE 
			WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),C1.C_Name, P.Part_Number,P.P_marketing_part_number
		FROM
	     ((((Customer_Open_Order_LineItem COOL INNER JOIN  Customer_Shipping_Line_Item_Table CSLIT ON
	        COOL.LineItem_ID = CSLIT.LineItem_ID)
	     INNER JOIN Sales_Order SO ON COOL.SalesOrder_ID = SO.SalesOrder_ID )--and SO.SO_Is_Sample = @order_type
	     INNER JOIN Product P ON COOL.Product_ID = P.Product_ID)
	     INNER JOIN Customer_Shipping_Table CST ON  CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID)
	     INNER JOIN Customer C1 ON SO.Customer_ID = C1.Customer_ID	     
		WHERE
	    CST.CST_Status = ''SHIPPED'' and so_is_consignment = 1  '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @ORDERTYPE IS NOT NULL AND len(@ORDERTYPE) > 0
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING +' AND CST.CST_SHIP_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + +' AND CST.CST_SHIP_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C1.C_Region ) = '''+ @REGION+''''
	END
	IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	

	EXEC (@SHIPMENTSTRING)
/* SHIPPING REPORT ENDS HERE */




IF @REPORTTYPE = 'XLREPORT'
BEGIN
		SET @RESULT_STR = 'SELECT
			  [CUSTOMER],[END USER],[END CUSTOMER],[SALES ORDER NO],[LINE ITEM NO],
			  [BOOKING DATE],[CHANGE DATE],
			  [REQUESTED DOCK DATE],
			  [SCHEDULE DATE],
			  [REVISED DATE],
			  [PRODUCT],[MRKT PART NUMBER],BOOKING_QTY,REMAINING_QTY,ACTUAL_SHIP_QTY,[UNIT PRICE],BACKLOG_AMOUNT, BOOKING_AMOUNT, SHIPPING_AMOUNT
		FROM
			#BOOKINGTABLE 
		where [CUSTOMER] is not null '

/*
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR +' AND [CHANGE DATE] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END
		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR + +' AND [CHANGE DATE] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		END
*/
		EXEC (@RESULT_STR)
END
ELSE
BEGIN

		SET @RESULT_STR = 'SELECT
			 SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,BOOKING_QTY,REMAINING_QTY,ACTUAL_SHIP_QTY
			  [UNIT PRICE],BACKLOG_AMOUNT, BOOKING_AMOUNT, SHIPPING_AMOUNT, [REQUESTED DOCK DATE],
			  [SCHEDULE DATE],
			  [REVISED DATE],
			  [BOOKING DATE],
			  [CHANGE DATE],[SHIP DATE], [CONFIRM DATE]
			  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
			  [REGION REP],[END USER],CUSTOMER, [PRODUCT], [MRKT PART NUMBER]
		FROM
			#BOOKINGTABLE 
		where [CUSTOMER] is not null '
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR +' AND [CHANGE DATE] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END
		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR + +' AND [CHANGE DATE] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		END
		EXEC (@RESULT_STR)
END

END


